﻿using HCQ2_Common;
using HCQ2_Common.SQL;
using HCQ2_IDAL;
using HCQ2_Model;
using HCQ2_Model.VR.Params;
using HCQ2_Model.VRDD.InputDto._0714;
using HCQ2_Model.VRDD.OutputDto._0714;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VRUserLevelDAL: IT_VRUserLevelDAL
    {
        /// <summary>
        ///  获取用户等级一栏数据
        /// </summary>
        /// <param name="param">查询参数</param>
        /// <param name="total">返回总的记录条数</param>
        /// <returns></returns>
        public List<T_VRUserLevel> GetUserLevelData(UserlevelParam param,out int total)
        {
            sb?.Clear();
            string whereSql = GetLevelSql(param);
            total = Helper.ToInt(SqlHelper.ExecuteScalar("SELECT COUNT(0)AS num FROM T_VRUserLevel WHERE 1=1 " + whereSql));
            sb.Append($@"SELECT TOP 10 * FROM (SELECT ROW_NUMBER() OVER(ORDER BY user_level ASC) AS number, * 
            FROM T_VRUserLevel WHERE 1=1 ");
            sb.Append(whereSql);
            sb.Append($")levels WHERE levels.number>{(param.page - 1) * param.rows}");
            return SqlHelper.ExecuteList<T_VRUserLevel>(sb.ToString());
        }
        private string GetLevelSql(UserlevelParam param)
        {
            StringBuilder _sb = new StringBuilder();
            //销售额
            if (param.minScore > 0 && param.maxScore > 0)
                _sb.Append($@" AND min_sale>={param.minScore} AND max_sale<={param.maxScore} ");
            else if(param.minScore > 0 && param.maxScore<=0)
                _sb.Append($@" AND min_sale>={param.minScore} ");
            else if(param.minScore<=0 && param.maxScore > 0)
                _sb.Append($@" AND max_sale<={param.maxScore} ");
            //下限人数
            if (param.minLine > 0 && param.maxLine > 0)
                _sb.Append($@" AND min_line>={param.minLine} AND max_line<={param.maxLine} ");
            else if (param.minScore > 0 && param.maxScore <= 0)
                _sb.Append($@" AND min_line>={param.minLine} ");
            else if (param.minScore <= 0 && param.maxScore > 0)
                _sb.Append($@" AND max_line<={param.maxLine} ");
            //用户等级
            if (param.userLevel > 0)
                _sb.Append($" AND user_level={param.userLevel} ");
            return _sb.ToString();
        }
        /// <summary>
        ///  获取积分列表
        /// </summary>
        /// <param name="param"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<UserIntegralLogModel> GetTotalGoldList(TotalLogParam param, out int total)
        {
            sb?.Clear();          
            StringBuilder _sb = new StringBuilder();
            if (!string.IsNullOrEmpty(param.userName))
                _sb.Append($" AND user_name='{param.userName}' ");
            if (!string.IsNullOrEmpty(param.shareUser))
                _sb.Append($" AND share_name='{param.shareUser}' ");
            if (!string.IsNullOrEmpty(param.orderCode))
                _sb.Append($" AND order_code='{param.orderCode}' ");
            if (!string.IsNullOrEmpty(param.fileCode))
                _sb.Append($" AND file_code='{param.fileCode}' ");
            if (!string.IsNullOrEmpty(param.startDate) && !string.IsNullOrEmpty(param.endDate))
                _sb.Append($" AND create_date>='{param.startDate}' AND create_date<='{param.endDate}' ");
            else if (!string.IsNullOrEmpty(param.startDate) && string.IsNullOrEmpty(param.endDate))
                _sb.Append($" AND create_date>='{param.startDate}' ");
            else if (string.IsNullOrEmpty(param.startDate) && !string.IsNullOrEmpty(param.endDate))
                _sb.Append($" AND create_date<='{param.endDate}' ");

            total = Helper.ToInt(SqlHelper.ExecuteScalar(" SELECT COUNT(0)AS num FROM T_VRUserIntegralLog WHERE 1=1 " + _sb.ToString()));
            sb.Append($@"SELECT  TOP {param.rows} l.*,f.folder_name FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY id)AS number FROM T_VRUserIntegralLog WHERE 1=1");
            sb.Append(_sb.ToString());
            sb.Append($@")l INNER JOIN (SELECT file_id,folder_id FROM dbo.T_DocumentFolderRelation)rel ON l.file_id=rel.file_id INNER JOIN
            (SELECT folder_id,folder_name FROM dbo.T_DocumentFolder)f ON rel.folder_id=f.folder_id WHERE l.number>{(param.page - 1) * param.rows}");
            return SqlHelper.ExecuteList<UserIntegralLogModel>(sb.ToString());


            //sb.Append($"SELECT  TOP {param.rows} * FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY id)AS number FROM T_VRUserIntegralLog WHERE 1=1 ");
            //sb.Append(_sb.ToString());
            //sb.Append($" )logs WHERE logs.number>{(param.page - 1) * param.rows}");
            //return SqlHelper.ExecuteList<T_VRUserIntegralLog>(sb.ToString());
        }
        /// <summary>
        ///  获取用户成长
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public UserLevelGrowUp GetUserGrowUp(InputUserLevelGrowUp param)
        {
            sb?.Clear();
            sb.Append($@"SELECT user_level,CONVERT(varchar(100),apply_date,120) AS apply_date,ref_name,line_num=(SELECT COUNT(0) FROM T_VRFounderApply WHERE ref_id={param.user_id}),
            sale_num=(SELECT SUM(gold_num)AS gold_num FROM T_VRGoldLog WHERE FolderId>0 AND ((gold_type=1 AND ISNULL(ParentOrderCode,'')='') OR ISNULL(ParentOrderCode,'')<>'')
              AND create_id={param.user_id} AND create_date>CAST(YEAR(GETDATE()) AS NVARCHAR(4))+'-01-01' AND create_date<=CAST(YEAR(GETDATE()) AS NVARCHAR(4))+'-12-31')
             FROM T_VRFounderApply WHERE user_id={param.user_id} AND folder_id={param.store_id};");
            return SqlHelper.ExecuteList<UserLevelGrowUp>(sb.ToString())?.FirstOrDefault();
        }
        /// <summary>
        ///  获取店铺根节点id
        /// </summary>
        /// <param name="store_id"></param>
        /// <returns></returns>
        public int GetRootIdByStore(int store_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT TOP 1 a.folder_id FROM dbo.T_DocumentFolder a INNER JOIN
                dbo.T_DocumentFolder b ON b.folder_path LIKE a.folder_path+'%' AND b.folder_id={store_id} ORDER BY a.folder_path;");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }
        /// <summary>
        ///  获取店铺根节点id
        /// </summary>
        /// <param name="store_id"></param>
        /// <returns></returns>
        public T_DocumentFolder GetRootIdByStore1(int store_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT TOP 1 a.folder_id FROM dbo.T_DocumentFolder a INNER JOIN
                dbo.T_DocumentFolder b ON b.folder_path LIKE a.folder_path+'%' AND b.folder_id={store_id} ORDER BY a.folder_path;");
            return SqlHelper.ExecuteList<T_DocumentFolder>(sb.ToString())?.FirstOrDefault();
        }
    }
}